use anyhow::anyhow;
use anyhow::{Ok, Result};
use log::info;
use rbase::db::{self, get_connection};
use rbase::id_utils::gen_id;
use rbase::time_utils::get_systemtime_millis;
use sea_orm::ActiveValue::Set;
use sea_orm::PaginatorTrait;
use sea_orm::QueryOrder;
use sea_orm::QuerySelect;
use sea_orm::prelude::Expr;
use sea_orm::{ColumnTrait, QueryFilter};
use sea_orm::{EntityTrait, Order};

use crate::db::entities::classroom::{ActiveModel, CREATE_QUERY, Column, Entity, Model, TABLE_COLUMNS, TABLE_NAME, get_column};

#[derive(Default)]
pub struct SearchParams {
    pub name: Option<String>,
    pub offset: Option<u64>,
    pub limit: Option<u64>,
    pub order_by: Option<String>,
    pub order: Option<String>,
    pub owner_ids: Option<Vec<i64>>,
}

pub async fn init_tables() -> Result<()> {
    info!("Initializing tables");
    db::init_tables(TABLE_NAME, CREATE_QUERY, &TABLE_COLUMNS).await?;
    Ok(())
}

pub async fn insert(model: Model) -> Result<Model> {
    let connection = get_connection()?;

    let mut active_model = ActiveModel::from(model);

    let id = gen_id()?;
    active_model.id = Set(id);

    let systemtime = get_systemtime_millis();
    active_model.created_at = Set(Some(systemtime));
    active_model.modified_at = Set(Some(systemtime));

    Entity::insert(active_model).exec(&connection).await?;
    let model = find_by_id(id).await?.ok_or_else(|| anyhow!("Failed to insert classroom"))?;
    Ok(model)
}

pub async fn update(model: Model) -> Result<()> {
    let connection = get_connection()?;

    let mut update = Entity::update_many();
    if let Some(name) = model.name {
        update = update.col_expr(Column::Name, Expr::value(name.clone()));
    };
    if let Some(owner_id) = model.owner_id {
        update = update.col_expr(Column::OwnerId, Expr::value(owner_id.clone()));
    };
    if let Some(grade) = model.grade {
        update = update.col_expr(Column::Grade, Expr::value(grade.clone()));
    };
    if let Some(class) = model.class {
        update = update.col_expr(Column::Class, Expr::value(class.clone()));
    };
    if let Some(created_at) = model.created_at {
        update = update.col_expr(Column::CreatedAt, Expr::value(created_at));
    };
    update = update.col_expr(Column::ModifiedAt, Expr::value(get_systemtime_millis()));
    let update_result = update.filter(Column::Id.eq(model.id)).exec(&connection).await?;

    if update_result.rows_affected == 1 {
        Ok(())
    } else {
        Err(anyhow!("Failed to update classroom"))
    }
}

pub async fn find_by_name(name: &str) -> Result<Option<Model>> {
    let connection = get_connection()?;

    Entity::find()
        .filter(Column::Name.eq(name))
        .one(&connection)
        .await
        .map_err(|e| anyhow!("Error finding classroom: {:?}", e))
}

pub async fn delete_by_id(id: i64) -> Result<()> {
    let connection = get_connection()?;

    Entity::delete(ActiveModel {
        id: Set(id),
        ..Default::default()
    })
    .exec(&connection)
    .await
    .map_err(|e| anyhow!("Error deleting classroom: {:?}", e))?;

    Ok(())
}

pub async fn find_by_id(id: i64) -> Result<Option<Model>> {
    let connection = get_connection()?;

    Entity::find()
        .filter(Column::Id.eq(id))
        .one(&connection)
        .await
        .map_err(|e| anyhow!("Error finding classroom: {:?}", e))
}

pub async fn find_by_id_and_owner_ids(id: i64, owner_ids: &Option<Vec<i64>>) -> Result<Option<Model>> {
    let connection = get_connection()?;

    Entity::find()
        .filter(match owner_ids {
            Some(owner_ids) => Column::Id.eq(id).add(Column::OwnerId.is_in(owner_ids.clone())),
            None => Column::Id.eq(id),
        })
        .one(&connection)
        .await
        .map_err(|e| anyhow!("Error finding classroom: {:?}", e))
}

pub async fn search(search_params: &SearchParams) -> Result<(Vec<Model>, i64)> {
    let connection = get_connection()?;

    let mut filter = Expr::value(true);
    let mut has_filter = false;

    if let Some(name) = &search_params.name {
        if !name.is_empty() {
            filter = filter.and(Column::Name.like(format!("%{}%", name)));
            has_filter = true;
        }
    }

    if let Some(owner_ids) = &search_params.owner_ids
        && owner_ids.len() > 0
    {
        filter = Column::OwnerId.is_in(owner_ids.clone());
        has_filter = true;
    }

    // 计算总数
    let count = if has_filter { Entity::find().filter(filter.clone()) } else { Entity::find() };
    let total = count.count(&connection).await?;
    if total <= 0 {
        return Ok((Vec::new(), 0));
    }

    // 分页计算
    let mut query = if has_filter { Entity::find().filter(filter) } else { Entity::find() };
    if let Some(offset) = search_params.offset {
        query = query.offset(offset);
    }
    if let Some(limit) = search_params.limit {
        query = query.limit(limit);
    }
    if let Some(order_by) = &search_params.order_by {
        let col = get_column(order_by);
        let order = match search_params.order.clone() {
            Some(order) => match order.as_str() {
                "asc" | "ascending" => Order::Asc,
                _ => Order::Desc,
            },
            None => Order::Desc,
        };
        query = query.order_by(col, order);
    }

    let models = query.all(&connection).await?;
    Ok((models, total as i64))
}
